package com.nit.lab.vote.mapper.vote;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.nit.lab.vote.dto.basic.UserDTO;
import com.nit.lab.vote.dto.utils.PasswordDTO;
import com.nit.lab.vote.dto.vote.CandidateDTO;
import com.nit.lab.vote.dto.vote.ProjectDTO;
import com.nit.lab.vote.entity.vote.ProjectEntity;
import com.nit.lab.vote.entity.vote.UPEntity;
import org.apache.catalina.User;
import org.apache.ibatis.annotations.*;
import com.nit.lab.vote.entity.vote.ProjectEntity;
import org.springframework.web.bind.annotation.RequestBody;

import java.util.Date;
import java.util.List;

public interface ProjectMapper {
    @Select("<script>Select *, project_id AS `key` from project " +
            "where 1=1 " +
            "<if test='projectName != null'> AND project_name LIKE CONCAT('%', #{projectName}, '%') </if> " +
            "<if test='sponsor != null'> AND sponsor LIKE CONCAT('%', #{sponsor}, '%') </if> " +
            "<if test='projectType != null'> AND project_type = #{projectType} </if> " +
            "<if test='state != null'> AND state = #{state} </if> " +
            "ORDER BY " +
            "<if test='sortField != null'>" +
            "${sortField} ${sortOrder}, " +
            "</if>" +
            "gmt_create desc " +
            "LIMIT #{start}, #{length}" +
            "</script>")
    List<ProjectDTO> listAll(
            @Param("projectName") String projectName,
            @Param("sponsor") String sponsor,
            @Param("projectType") Long projectType,
            @Param("state") Long state,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;

    @Select("<script>Select count(*) " +
            "From project " +
            "where 1=1 " +
            "<if test='projectName != null'> AND project_name LIKE CONCAT('%', #{projectName}, '%') </if> " +
            "<if test='sponsor != null'> AND sponsor LIKE CONCAT('%', #{sponsor}, '%') </if> " +
            "<if test='projectType != null'> AND project_type = #{projectType} </if> " +
            "<if test='state != null'> AND state = #{state} </if> " +
            "</script>")
    Long count4Table1(
            @Param("projectName") String projectName,
            @Param("sponsor") String sponsor,
            @Param("projectType") Long projectType,
            @Param("state") Long state) throws Exception;

    @Select("<script>Select *, project.project_id AS `key`,voted from project " +
            "LEFT JOIN user_project ON user_project.project_id = project.project_id " +
            "where state = 1 and user_project.user_id = #{userId} " +
            "<if test='projectName != null'> AND project_name LIKE CONCAT('%', #{projectName}, '%') </if> " +
            "<if test='sponsor != null'> AND sponsor LIKE CONCAT('%', #{sponsor}, '%') </if> " +
            "<if test='type != null'> AND type LIKE CONCAT('%', #{type}, '%') </if> " +
            "<if test='subject != null'> AND subject LIKE CONCAT('%', #{subject}, '%') </if> " +
            "<if test='state != null'> AND state = #{state} </if> " +
            "ORDER BY " +
            "<if test='sortField != null'>" +
            "${sortField} ${sortOrder}, " +
            "</if>" +
            "user_id DESC " +
            "LIMIT #{start}, #{length}" +
            "</script>")
    List<ProjectDTO> listUserProject(
            @Param("userId") Long userId,
            @Param("projectName") String projectName,
            @Param("sponsor") String sponsor,
            @Param("type") String type,
            @Param("subject") String subject,
            @Param("state") Long state,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;

    @Select("<script>Select count(*) " +
            "From project " +
            "LEFT JOIN user_project ON user_project.project_id = project.project_id " +
            "where 1=1 and user_project.user_id = #{userId} " +
            "<if test='projectName != null'> AND project_name LIKE CONCAT('%', #{projectName}, '%') </if> " +
            "<if test='sponsor != null'> AND sponsor LIKE CONCAT('%', #{sponsor}, '%') </if> " +
            "<if test='type != null'> AND type LIKE CONCAT('%', #{type}, '%') </if> " +
            "<if test='subject != null'> AND subject LIKE CONCAT('%', #{subject}, '%') </if> " +
            "<if test='state != null'> AND state = #{state} </if> " +
            "</script>")
    Long count4Table(
            @Param("userId") Long userId,
            @Param("projectName") String projectName,
            @Param("sponsor") String sponsor,
            @Param("type") String type,
            @Param("subject") String subject,
            @Param("state") Long state) throws Exception;

    @Update("Update project set " +
            "state = #{entity.state} ," +
            "gmt_create = #{date} " +
            "where project_id = #{entity.projectId} ")
    Integer update(
            @Param("entity") ProjectEntity entity,
            @Param("date") String date
    ) throws Exception;

    @Update("Update project set " +
            "projectName = #{entity.projectName}," +
            "type = #{entity.type} ," +
            "subject = #{entity.subject} ," +
            "date = #{entity.date} " +
            "gmt_create = #{date} " +
            "where project_id = #{entity.projectId} ")
    Integer updateProject(
            @Param("entity") ProjectEntity entity,
            @Param("date") String date
    ) throws Exception;

    @Insert("INSERT INTO project (" +
            " project_name , " +
            "sponsor,  project_type, " +
            "max_vote, date, state, gmt_create) " +
            "VALUES (#{projectEntity.projectName}, " +
            "#{sponsor}, #{projectEntity.projectType}, " +
            "#{projectEntity.maxVote}, #{projectEntity.date}, " +
            "0, #{date})")
    Integer addProject(
            @Param("projectEntity") ProjectEntity projectEntity,
            @Param("sponsor") String sponsor,
            @Param("date") String date

    ) throws Exception;

    /**
     * @Description: 删除记录
     * @Date: 2020/12/12
     */

    @Delete("DELETE FROM Project WHERE project_id = #{entity.projectId} ")
    Integer deleteProject(@Param("entity") ProjectEntity entity) throws Exception;


    @Select({"<script> SELECT *, u.user_id AS `key` " +
            "FROM user u " +
            "JOIN user_project up ON u.user_id = up.user_id " +
            "WHERE 1 = 1 " +
            "<if test = 'projectId != null'> AND project_id = #{projectId}</if> " +
            "<if test = 'userName != null'> AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "ORDER BY " +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if>" +
            "u.user_id DESC " +
            "</script>"})
    List<UserDTO> listByProject(
            @Param("userName") String userName,
            @Param("projectId") Long projectId,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Select("<script>SELECT count(*) " +
            "FROM user u " +
            "JOIN user_project up ON u.user_id = up.user_id " +
            "WHERE true " +
            "<if test='projectId != null'> AND up.project_id = #{projectId} </if> " +
            "<if test = 'userName != null'> AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "</script>")
    Long countByProject(
            @Param("projectId") Long projectId,
            @Param("userName") String userName
    ) throws Exception;

    @Select({"<script> SELECT *, u.user_id AS `key`, n.project_id AS `projectId` " +
            "FROM user u " +
            "Left JOIN (select * from user_project up " +
            "WHERE 1 = 1 " +
            "<if test = 'projectId != null'> AND project_id = #{projectId}</if> " +
            " ) n on n.user_id = u.user_id " +
            "WHERE role_id = 2 " +
            "<if test = 'userName != null'>AND user_name LIKE CONCAT('%', #{userName}, '%') </if> " +
            "ORDER BY " +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if>" +
            "n.project_id DESC, u.user_id DESC " +
            "</script>"})
    List<UserDTO> listAllProject(
            @Param("userName") String userName,
            @Param("projectId") Long projectId,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Select("<script>SELECT count(*) " +
            "FROM user u " +
            "WHERE role_id = 2 " +
            "<if test = 'userName != null'> AND user_name LIKE CONCAT('%', #{userName}, '%') </if> " +
            "</script>")
    Long countAllProject(
            @Param("userName") String candidateName
    ) throws Exception;

    @Delete("DELETE FROM user_project " +
            "WHERE user_id = #{entity.userId} AND project_id = #{entity.projectId}")
    Integer removeByProject(@Param("entity") UPEntity upEntity) throws Exception;

    @Insert("INSERT INTO user_project " +
            "(user_id, project_id, voted) " +
            "values" +
            "(#{entity.userId}, #{entity.projectId}, #{entity.voted})")
    @Options(useGeneratedKeys = true, keyProperty = "userProjectId", keyColumn = "user_project_id")
    Integer addByProject(@Param("entity") UPEntity upEntity) throws Exception;

    @Select({"<script> SELECT " +
            " u.user_id AS `key`, " +
            " u.user_name AS `name`, " +
            " u.phone AS `phone`, " +
            " u.original AS `password` " +
            "FROM " +
            "user u " +
            "JOIN ( SELECT * FROM user_project up WHERE 1 = 1 " +
            "<if test = 'projectId != null'> AND project_id = #{projectId}</if>" +
            ") n ON n.user_id = u.user_id " +
            "WHERE " +
            "role_id = 2 " +
            "<if test = 'userName != null'>AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "ORDER BY" +
            "<if test = 'sortField != null'> " +
            "${sortField} ${sortOrder}, </if>" +
            "n.project_id DESC " +
            "</script>"})
    List<PasswordDTO>listAllProject4Download(
            @Param("userName") String userName,
            @Param("projectId") Long projectId,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Select("SELECT * from project " +
            "WHERE project_id = #{projectId} LIMIT 1")
    ProjectDTO getProjectById(@Param("projectId")Long projectId) throws Exception;
}
